/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import ExcelAccess.ExcelFormat;
import Message.LoginMessage;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 *
 * @author workshop
 */
public class DB {
    Connection conn = null;
    
    public void connectToDB(LoginMessage loginMsn) throws ClassNotFoundException, SQLException, Exception{
        Class.forName("com.mysql.jdbc.Driver");
        conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/huisman",
                "root","root");
        Statement st = (Statement) conn.createStatement();
        ResultSet rs = st.executeQuery("SELECT username,password,securitylevel FROM persons where username = " + 
                "\'" + loginMsn.username + "\' and password = \'" + loginMsn.password +"\';");
        
        if(!rs.next()) throw new Exception("username or password is wrong");
        System.out.println("User " + loginMsn.username + " got connected!");
        loginMsn.securitylevel = rs.getInt("securitylevel");
        conn.setAutoCommit(true);
        rs.close();
        st.close();
    }
    
    public ArrayList<String> searchDB(String query,int numField) throws SQLException, Exception{
        if(conn == null){
            throw new Exception();
        }
        Statement st = (Statement) conn.createStatement();
        ResultSet rs = st.executeQuery(query);
        ArrayList<String> queryAns = new ArrayList<>();
        while(rs.next()){
            for(int i = 1; i <= numField; i++) queryAns.add(rs.getString(i));
        }
        System.out.println("Search query finished\n");
        rs.close();
        st.close();
        return queryAns;
    }
    
    public void updateDB(String query) throws SQLException, Exception{
        if(conn == null){
            throw new Exception();
        }
        Statement st = (Statement) conn.createStatement();
        st.executeUpdate(query);
        st.close();
    }
    
    // only object name exist
    public boolean isObjectNumberExist(String objectNumber) throws SQLException{
        ArrayList<String> result = this.getObjectInfo(objectNumber);
        return (result != null);
    }

    // only object exist
    public boolean isObjectExist(ArrayList<String> fieldValues) throws SQLException{
        String query = "Select * from objects where ";
        String[] fieldNames = ExcelFormat.convertFieldNames(ExcelFormat.objectsFields);
        int nonEmptyElement = ExcelFormat.getNumNonEmptyElement(fieldValues);
        int counter = 0;
        
        for(int i = 0; i < ExcelFormat.objectsFields.length; i++){
            String input = fieldValues.get(i);
            if(!input.isEmpty()){
                if(ExcelFormat.objectsFields[i].equalsIgnoreCase("year built")){
                    query += fieldNames[i] + " = " + input;
                }else{
                    query += fieldNames[i] + " = " + "\'" + input + "\'";
                }
                counter++;
            }
            if(counter != nonEmptyElement){
                query += " and ";
            }
        }
        query += ";";
        Statement st = (Statement) conn.createStatement();
        ResultSet rs = st.executeQuery(query);
        if(rs.next()){
            rs.close();
            st.close();
            return true;
        }
        rs.close();
        st.close();
        return false;
    }
    
    public ArrayList<String> getObjectInfo(String objectName) throws SQLException{
        ArrayList<String> result = new ArrayList<>();
        String query = "Select * from objects where Object = \'" + objectName + "\';";
        Statement st = (Statement) conn.createStatement();
        ResultSet rs = st.executeQuery(query);
        if(rs.next()){
            for(int i = 1; i <= ExcelFormat.objectsFields.length; i++) result.add(rs.getString(i));
            rs.close();
            st.close();
            return result;
        }
        rs.close();
        st.close();
        return null;
    }
    
    public ArrayList<String> getObjectAllServiceCall(String objectName) throws SQLException{
        ArrayList<String> result = new ArrayList<>();
        String query = "Select * from servicerecords where Object = \'" + objectName + "\';";
        Statement st = (Statement) conn.createStatement();
        ResultSet rs = st.executeQuery(query);
        while(rs.next()){
            for(int i = 1; i <= ExcelFormat.serviceCallFields.length; i++) result.add(rs.getString(i));
        }
        rs.close();
        st.close();
        return result;
    }
    
    public void addObject(ArrayList <String> fieldValues) throws SQLException{
        String query = "Insert into objects values (";
        for (int i=0; i<ExcelFormat.objectsFields.length;i++){
            String input = fieldValues.get(i);
            if (!input.isEmpty())
                query += "\'" + input+ "\'" ;
            else
                query += "null";
            if (i!=ExcelFormat.objectsFields.length-1)
                query +=",";            
        }
        query += ");";
        Statement st = (Statement) conn.createStatement();
        st.executeUpdate(query);
        st.close();    
    
    }
    
    public void updateObject(ArrayList<String> fieldValues) throws SQLException{
        String query = "Update objects set ";
        String[] fieldNames = ExcelFormat.convertFieldNames(ExcelFormat.objectsFields);
        int nonEmptyElement = ExcelFormat.getNumNonEmptyElement(fieldValues);
        int counter = 0;
        
        for(int i = 0; i < ExcelFormat.objectsFields.length; i++){
            String input = fieldValues.get(i);
            if(!input.isEmpty()){
                if(ExcelFormat.objectsFields[i].equalsIgnoreCase("year built")){
                    query += fieldNames[i] + " = " + input;
                }else{
                    query += fieldNames[i] + " = " + "\'" + input + "\'";
                }
                counter++;
            }
            if(counter != nonEmptyElement){
                query += ",";
            }
        }
        query += " where object = " + fieldValues.get(0) + ";";
        Statement st = (Statement) conn.createStatement();
        st.executeUpdate(query);
        st.close();
    }
    
    public void addServiceCallsBulk(ArrayList<String> fieldValues) throws SQLException{
        try {
            conn.setAutoCommit(false);
            
            Statement st = (Statement) conn.createStatement();
            int rows = fieldValues.size() / ExcelFormat.serviceCallFields.length;
            for(int i = 0; i < rows; i++){
                String query = "insert into " + "servicerecords values(";
                for(int j = 0; j < ExcelFormat.serviceCallFields.length; j++){
                    String input = fieldValues.get(i*ExcelFormat.serviceCallFields.length+j);
                    if(input.isEmpty()){
                        query += "null";
                    }else{
                        query += "\'" + input + "\'";
                    }
                    if(j != ExcelFormat.serviceCallFields.length - 1)
                        query += ",";
                }
                query += ");";
                st.executeUpdate(query);
            }
            st.close();
        } catch (SQLException ex) {
            conn.rollback();
            conn.setAutoCommit(true);
            throw ex;
        }
        conn.setAutoCommit(true);
    }
}
